package com.china08.yxyapi.service.db.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.mongodb.core.MongoOperations;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.entity.mg.lecture.YxyLecturePrePack;
import com.china08.yxyapi.service.db.YxyLecturePrePackService;

@Service
public class YxyLecturePrePackServiceImpl implements YxyLecturePrePackService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    MongoOperations mongoOperations;

    @Override
    public List<String> findPrePackIdForYunXiao(Integer pageNum,
            Integer pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("   where status = 1                          ");
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");
        sb.append("  limit ?,?                                  ");
        Integer start = pageNum * pageSize;
        Integer end = (pageNum + 1) * pageSize;
        return jdbcTemplate.queryForList(sb.toString(),
                new Object[] { start, end }, String.class);
    }

    // 教育局-纠错
    @Override
    public List<String> findYxyLecturePrePackIdAndStatus(String phaseId,
            String subjectId, String keyword, Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            int i, Integer order, int page, int size) {

        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(areaIdProvince);
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where area_id_province = ?                 ");
        if (bureauLevel == 1) {
            sb.append("   and area_id_city = ?                  ");
            paramList.add(areaIdCity);
        }
        if (bureauLevel == 0) {
            sb.append("   and area_id_city = ?                  ");
            sb.append("   and area_id_district = ?              ");
            paramList.add(areaIdCity);
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  pack_name like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 1                             ");
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("  group by prepack_id                        ");
        if (order == 0) {
            sb.append("  order by created_date desc                 ");
        } else {
            sb.append("  order by created_date asc                 ");
        }

        sb.append("  limit ?,?                                  ");
        Integer start = page * size;
        Integer end = (page + 1) * size;
        paramList.add(start);
        paramList.add(end);
        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    @Override
    public long countByPrePackIdAndStatus(Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            Integer status) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(areaIdProvince);
        sb.append("  select                                         ");
        sb.append("    count(prepack_id)                            ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back         ");
        sb.append("  where area_id_province = ?                     ");
        if (bureauLevel == 1) {
            sb.append("  and area_id_city = ?                       ");
            paramList.add(areaIdCity);
        }
        if (bureauLevel == 0) {
            sb.append("  and area_id_city = ?                       ");
            sb.append("  and area_id_district = ?                   ");
            paramList.add(areaIdCity);
            paramList.add(areaIdDistrict);

        }
        sb.append("   and status = 1                                ");
        sb.append("  group by prepack_id                            ");
        List<Map<String, Object>> list = jdbcTemplate
                .queryForList(sb.toString(), paramList.toArray());
        return list.size();
    }

    @Override
    public long countByPrePackIdAndStatusForYunXiao() {
        StringBuffer sb = new StringBuffer();
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("   where status = 1                          ");
        sb.append("  group by prepack_id                        ");
        List<Map<String, Object>> list = jdbcTemplate
                .queryForList(sb.toString());
        return list.size();
    }

    @Override
    public Map<String, Object> findForYunXiao(String phaseId, String subjectId,
            String keyword, Integer statu, Integer order, Integer pageNum,
            Integer pageSize) {
        Integer skip = (pageNum) * pageSize;

        Criteria criteria = new Criteria();
        criteria.and("share").is(1);
        criteria.and("status").is(statu);

        if (StringUtils.isNotBlank(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }
        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(pageSize);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    @Override
    public Map<String, Object> findForEDU(Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            String phaseId, String subjectId, Integer status, Integer order,
            String keyword, Integer pageNum, Integer pageSize) {
        Integer skip = (pageNum) * pageSize;

        Criteria criteria = new Criteria();
        criteria.and("share").is(1);
        criteria.and("auditStats").is(1);

        if (bureauLevel == 2) {
            criteria.and("areaIdProvince").is(areaIdProvince);
        }
        if (bureauLevel == 1) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
        }
        if (bureauLevel == 0) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
            criteria.and("areaIdDistrict").is(areaIdDistrict);
        }
        if (null != status) {
            criteria.and("status").is(status);
        }
        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(pageSize);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (pageNum) * pageSize;

            criteria = new Criteria();
            criteria.and("share").is(1);
            criteria.and("auditStats").is(1);

            if (bureauLevel == 2) {
                criteria.and("areaIdProvince").is(areaIdProvince);
            }
            if (bureauLevel == 1) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
            }
            if (bureauLevel == 0) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
                criteria.and("areaIdDistrict").is(areaIdDistrict);
            }
            if (null != status) {
                criteria.and("status").is(status);
            }
            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(pageSize);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }
        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    // 待审核
    @Override
    public Map<String, Object> findForWaitAudit(Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            String phaseId, String subjectId, Integer order, String keyword,
            int page, int size) {
        Integer skip = (page) * size;

        Criteria criteria = new Criteria();
        criteria.and("share").is(1);
        criteria.and("status").is(1);
        criteria.and("auditStats").is(0);

        if (bureauLevel == 2) {
            criteria.and("areaIdProvince").is(areaIdProvince);
        }
        if (bureauLevel == 1) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
        }
        if (bureauLevel == 0) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
            criteria.and("areaIdDistrict").is(areaIdDistrict);
        }

        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(size);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (page) * size;

            criteria = new Criteria();
            criteria.and("share").is(1);
            criteria.and("status").is(1);
            criteria.and("auditStats").is(0);

            if (bureauLevel == 2) {
                criteria.and("areaIdProvince").is(areaIdProvince);
            }
            if (bureauLevel == 1) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
            }
            if (bureauLevel == 0) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
                criteria.and("areaIdDistrict").is(areaIdDistrict);
            }

            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(size);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }

        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    // 审核未通过
    @Override
    public Map<String, Object> findForAuditNotPass(Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            String phaseId, String subjectId, Integer order, String keyword,
            int page, int size) {
        Integer skip = (page) * size;

        Criteria criteria = new Criteria();
        criteria.and("share").is(1);
        criteria.and("status").is(1);
        criteria.and("auditStats").is(2);

        if (bureauLevel == 2) {
            criteria.and("areaIdProvince").is(areaIdProvince);
        }
        if (bureauLevel == 1) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
        }
        if (bureauLevel == 0) {
            criteria.and("areaIdProvince").is(areaIdProvince);
            criteria.and("areaIdCity").is(areaIdCity);
            criteria.and("areaIdDistrict").is(areaIdDistrict);
        }

        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(size);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (page) * size;

            criteria = new Criteria();
            criteria.and("share").is(1);
            criteria.and("status").is(1);
            criteria.and("auditStats").is(2);

            if (bureauLevel == 2) {
                criteria.and("areaIdProvince").is(areaIdProvince);
            }
            if (bureauLevel == 1) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
            }
            if (bureauLevel == 0) {
                criteria.and("areaIdProvince").is(areaIdProvince);
                criteria.and("areaIdCity").is(areaIdCity);
                criteria.and("areaIdDistrict").is(areaIdDistrict);
            }

            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(size);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }
        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    // 学校-上架备课吧
    @Override
    public Map<String, Object> findForSCH(String schoolId, String phaseId,
            String subjectId, Integer status, Integer order, String keyword,
            int page, int size) {
        Integer skip = 0;
        Criteria criteria = null;
        Query query = null;
        long count = 0;
        List<YxyLecturePrePack> list = new ArrayList<YxyLecturePrePack>();

        //
        skip = (page) * size;

        criteria = new Criteria();
        criteria.and("share").is(2);
        criteria.and("auditStats").is(1);

        if (StringUtils.isNotBlank(schoolId)) {
            criteria.and("schoolId").is(schoolId);
        }
        if (null != status) {
            criteria.and("status").is(status);
        }
        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(size);
        list = mongoOperations.find(query, YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (page) * size;

            criteria = new Criteria();
            criteria.and("share").is(2);
            criteria.and("auditStats").is(1);

            if (StringUtils.isNotBlank(schoolId)) {
                criteria.and("schoolId").is(schoolId);
            }
            if (null != status) {
                criteria.and("status").is(status);
            }
            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(size);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }

        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    // 学校-待审核
    @Override
    public Map<String, Object> findForWaitAuditSCH(String schoolId,
            String phaseId, String subjectId, Integer order, String keyword,
            int page, int size) {
        Integer skip = 0;

        skip = (page) * size;

        Criteria criteria = new Criteria();
        criteria.and("share").is(2);
        criteria.and("status").is(1);
        criteria.and("auditStats").is(0);

        if (StringUtils.isNotBlank(schoolId)) {
            criteria.and("schoolId").is(schoolId);
        }

        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(size);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (page) * size;

            criteria = new Criteria();
            criteria.and("share").is(2);
            criteria.and("status").is(1);
            criteria.and("auditStats").is(0);

            if (StringUtils.isNotBlank(schoolId)) {
                criteria.and("schoolId").is(schoolId);
            }

            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(size);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }
        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    @Override
    public Map<String, Object> findForAuditNotPassSCH(String schoolId,
            String phaseId, String subjectId, Integer order, String keyword,
            int page, int size) {
        Integer skip = (page) * size;

        Criteria criteria = new Criteria();
        criteria.and("share").is(2);
        criteria.and("status").is(1);
        criteria.and("auditStats").is(2);

        if (StringUtils.isNotBlank(schoolId)) {
            criteria.and("schoolId").is(schoolId);
        }
        if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
            criteria.and("phaseId").is(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            criteria.and("subjectId").is(subjectId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            criteria.and("packName").regex(".*?" + keyword + ".*");
        }

        Query query = new Query();
        query.addCriteria(criteria);
        if (order == 0) {
            query.with(new Sort(new Order(Direction.DESC, "createdDate")));
        } else {
            query.with(new Sort(new Order(Direction.ASC, "createdDate")));
        }

        // 总条数
        long count = mongoOperations.count(query, YxyLecturePrePack.class);

        // 分页
        query.skip(skip);
        query.limit(size);
        List<YxyLecturePrePack> list = mongoOperations.find(query,
                YxyLecturePrePack.class);

        if (list.size() == 0) {
            skip = (page) * size;

            criteria = new Criteria();
            criteria.and("share").is(2);
            criteria.and("status").is(1);
            criteria.and("auditStats").is(2);

            if (StringUtils.isNotBlank(schoolId)) {
                criteria.and("schoolId").is(schoolId);
            }
            if (StringUtils.isNotBlank(phaseId) && !"00".equals(phaseId)) {
                criteria.and("phaseId").is(phaseId);
            }
            if (StringUtils.isNotBlank(subjectId)) {
                criteria.and("subjectId").is(subjectId);
            }
            if (StringUtils.isNotBlank(keyword)) {
                criteria.and("userNick").regex(".*?" + keyword + ".*");
            }

            query = new Query();
            query.addCriteria(criteria);
            if (order == 0) {
                query.with(new Sort(new Order(Direction.DESC, "createdDate")));
            } else {
                query.with(new Sort(new Order(Direction.ASC, "createdDate")));
            }

            // 总条数
            count = mongoOperations.count(query, YxyLecturePrePack.class);

            // 分页
            query.skip(skip);
            query.limit(size);
            list = mongoOperations.find(query, YxyLecturePrePack.class);
        }

        // 返回
        Map<String, Object> returnMap = new HashMap<String, Object>();
        returnMap.put("count", count);
        returnMap.put("list", list);
        return returnMap;
    }

    // 学校-纠错
    @Override
    public List<String> findYxyLecturePrePackIdAndStatusSCH(String phaseId,
            String subjectId, String keyword, String schoolId, int i,
            Integer order, int page, int size) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";

        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where 1 = 1                                ");

        if (StringUtils.isNotBlank(schoolId)) {
            sb.append(" and  school_id = ?                 ");
            paramList.add(schoolId);
        }
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 2                             ");
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  pack_name like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("  group by prepack_id                        ");
        if (order == 0) {
            sb.append("  order by created_date desc                 ");
        } else {
            sb.append("  order by created_date asc                 ");
        }

        sb.append("  limit ?,?                                  ");
        Integer start = page * size;
        Integer end = (page + 1) * size;
        paramList.add(start);
        paramList.add(end);

        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    @Override
    public long countByPrePackIdAndStatusSCH(String schoolId, int i) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        sb.append("  select                                         ");
        sb.append("    count(prepack_id)                            ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back         ");
        sb.append("  where 1 = 1                                    ");

        if (StringUtils.isNotBlank(schoolId)) {
            sb.append("  and school_id = ?                 ");
            paramList.add(schoolId);
        }
        sb.append("   and status = 0                                ");
        sb.append("  group by prepack_id                            ");
        List<Map<String, Object>> list = jdbcTemplate
                .queryForList(sb.toString(), paramList.toArray());
        return list.size();
    }

    // 学校-纠错模糊第二个条件
    @Override
    public List<String> findYxyLecturePrePackIdAndStatusSCH1(String phaseId,
            String subjectId, String keyword, String schoolId, int i, int page,
            int size) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";

        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where 1 = 1                                ");

        if (StringUtils.isNotBlank(schoolId)) {
            sb.append(" and  school_id = ?                 ");
            paramList.add(schoolId);
        }
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 2                             ");
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  user_nick like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");
        sb.append("  limit ?,?                                  ");
        Integer start = page * size;
        Integer end = (page + 1) * size;
        paramList.add(start);
        paramList.add(end);

        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    @Override
    public List<String> findYxyLecturePrePackIdAndStatus1(String phaseId,
            String subjectId, String keyword, Integer bureauLevel,
            String areaIdProvince, String areaIdCity, String areaIdDistrict,
            int i, int page, int size) {
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(areaIdProvince);
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where area_id_province = ?                 ");
        if (bureauLevel == 1) {
            sb.append("   and area_id_city = ?                  ");
            paramList.add(areaIdCity);
        }
        if (bureauLevel == 0) {
            sb.append("   and area_id_city = ?                  ");
            sb.append("   and area_id_district = ?              ");
            paramList.add(areaIdCity);
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  user_nick like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 1                             ");
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");
        sb.append("  limit ?,?                                  ");
        Integer start = page * size;
        Integer end = (page + 1) * size;
        paramList.add(start);
        paramList.add(end);
        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    // 学校-小红点
    @Override
    public List<String> findRedSCH(String phaseId, String subjectId,
            String keyword, String schoolId, int i) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";

        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where 1 = 1                                ");

        if (StringUtils.isNotBlank(schoolId)) {
            sb.append(" and  school_id = ?                 ");
            paramList.add(schoolId);
        }
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 2                             ");
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  pack_name like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");

        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    // 学校-小红点1
    @Override
    public List<String> findRedSCH1(String phaseId, String subjectId,
            String keyword, String schoolId, int i) {
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";

        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where 1 = 1                                ");

        if (StringUtils.isNotBlank(schoolId)) {
            sb.append(" and  school_id = ?                 ");
            paramList.add(schoolId);
        }
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 2                             ");
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  user_nick like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");

        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    @Override
    public List<String> findErrerRedEdu(String phaseId, String subjectId,
            String keyword, Integer bureauLevel, String areaIdProvince,
            String areaIdCity, String areaIdDistrict, int i) {
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(areaIdProvince);
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where area_id_province = ?                 ");
        if (bureauLevel == 1) {
            sb.append("   and area_id_city = ?                  ");
            paramList.add(areaIdCity);
        }
        if (bureauLevel == 0) {
            sb.append("   and area_id_city = ?                  ");
            sb.append("   and area_id_district = ?              ");
            paramList.add(areaIdCity);
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  pack_name like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 1                             ");
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");
        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

    @Override
    public List<String> findErrerRedEdu1(String phaseId, String subjectId,
            String keyword, Integer bureauLevel, String areaIdProvince,
            String areaIdCity, String areaIdDistrict, int i) {
        String keyword1 = (StringUtils.isNotBlank(keyword))
                ? "%" + keyword + "%" : "";
        StringBuffer sb = new StringBuffer();
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(areaIdProvince);
        sb.append("  select                                     ");
        sb.append("    prepack_id                               ");
        sb.append("  from yxy_yxy.yxy_yxy_lecture_feed_back     ");
        sb.append("  where area_id_province = ?                 ");
        if (bureauLevel == 1) {
            sb.append("   and area_id_city = ?                  ");
            paramList.add(areaIdCity);
        }
        if (bureauLevel == 0) {
            sb.append("   and area_id_city = ?                  ");
            sb.append("   and area_id_district = ?              ");
            paramList.add(areaIdCity);
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword1)) {
            sb.append(" and  user_nick like  ?                 ");
            paramList.add(keyword1);
        }
        sb.append("   and status = 1                            ");
        sb.append("   and share = 1                             ");
        if (StringUtils.isNotBlank(phaseId)) {
            sb.append(" and  phase_id = ?                 ");
            paramList.add(phaseId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            sb.append(" and  subject_id = ?                 ");
            paramList.add(subjectId);
        }
        sb.append("  group by prepack_id                        ");
        sb.append("  order by created_date desc                 ");
        return jdbcTemplate.queryForList(sb.toString(), paramList.toArray(),
                String.class);
    }

}
